<?php
/**
 * PointsController is used for management of points.
 * 
 * LBB Solutions Oy 版权所有 © 2013
 */
class PointsController extends Controller
{
	/**
	 * @return array action filters
	 */
	public function filters() {
		return array(
			'accessControl', // perform access control for CRUD operations
		);
	}
	
	/**
	 * Specifies the access control rules.
	 * This method is used by the 'accessControl' filter.
	 * @return array access control rules
	 */
	public function accessRules() {
		return array(
			array('allow', // allow authenticated user to perform below actions
				'users'=>array('@'),
			),
			array('deny',  // deny all users
				'users'=>array('*'),
			),
		);
	}
	
	/**
	 * Show layout.
	 */
	public function actionTo() {
		$model = new RewardForm();
		$this->render('to', array('model'=>$model));
	}
	
	/**
	 * Show layout.
	 */
	public function actionFrom() {
		$model = new RewardForm();
		$this->render('from', array('model'=>$model));
	}
	
	/**
	 * Redeem points from customers. Only ajax.
	 */
	public function actionRedeem() {
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		
		$r = null;
		$model = new RewardForm();
		
		// collect user input data
		if (isset($_POST['RewardForm'])) {
			$model->points = str_replace('-', '', trim($_POST['RewardForm']['points']));
			$model->customer = trim($_POST['RewardForm']['customer']);
			
			// validate user input and redirect to the previous page if valid
			if ($model->validate()) {
				// !important
				// convert mobile phone number: $model->customer
				try {
					$input_type = CustomerHelper::getType($model->customer);
					if (2 == $input_type) {
						$model->customer = CustomerHelper::formatMobile($model->customer);
					}
				} catch (Exception $ex) {
					throw new CHttpException(500, '{"customer":["'.$ex->getMessage().'"]}');
				}
				
				//start to checking the customers
				$sql_customers = 'SELECT id, email, mobile FROM tbl_user_account
				  WHERE id=:customer OR email=:customer OR mobile=:customer
				  ORDER BY created_time DESC ';
				try {
					$connection = Yii::app()->db;
					$command = $connection->createCommand($sql_customers);
					$rows = $command->queryAll(true, array(':customer'=>$model->customer));
					if (count($rows) == 0) {
						//there is no such user found, throw error
						throw new Exception('{"customer":["Cannot find this customer: '.$model->customer.'."]}');
					} elseif (count($rows) == 1) {
						//only one customer
						$row = $rows[0];
					} else {
						//there are N user profiles???
						$row = $rows[0];
						//trace error
						Yii::log('Merchant '.Yii::app()->user->id.' redeem points to the latest user: ' . $row['id'] . '. Find users: ' . print_r($rows, 1),
							CLogger::LEVEL_WARNING, 'web.reward.assign');
					}

					//new parameters
					$params = array(':id_user'=>$row['id'], ':id_merchant'=>Yii::app()->user->id);
					//get the points from customer
					$sql_assigned_points = 'SELECT t.id_user, u.name, u.email, u.mobile, u.picture, SUM(t.amount) AS amounts, COUNT(t.id) AS visit_times,
						MAX(UNIX_TIMESTAMP(t.create_time)) AS ts_last_time,
						(SELECT SUM(t1.point) FROM tbl_visit_transaction t1 WHERE t1.id_user=:id_user AND t1.id_merchant=:id_merchant AND t1.type=1) AS all_points,
						(SELECT SUM(t2.point) FROM tbl_visit_transaction t2 WHERE t2.id_user=:id_user AND t2.id_merchant=:id_merchant AND t2.type=2) AS spend_points
						FROM tbl_visit_transaction t
						LEFT JOIN tbl_user_account u ON u.id=t.id_user
						WHERE t.id_user=:id_user AND t.id_merchant=:id_merchant';
					$command = $connection->createCommand($sql_assigned_points);
					$r = $command->queryRow(true, $params);
					$no_redeem = true;
					
					$current_points = $r['all_points'] + $r['spend_points'];
					if ($current_points >= $model->points) {
						//1. redeem points with '-'
						$sql_points = 'INSERT INTO tbl_visit_transaction(id_user, id_merchant, point, type) VALUES(:id_user, :id_merchant, :point, 2)';
						$params_visit = array(
							':id_user' => $row['id'],
							':id_merchant' => Yii::app()->user->id,
							':point' => '-' . $model->points,
						);
						$command = $connection->createCommand($sql_points);
						$command->execute($params_visit);
						
						$current_points = $current_points - $model->points;
						$redeemed_points = -$r['spend_points'] + $model->points;

						//2. update into summary table
						$params = array(':id_user'=>$row['id'], ':id_merchant'=>Yii::app()->user->id);
						$sql_points_sum_del = 'DELETE FROM tbl_visit_customer_notes WHERE id_user=:id_user AND id_merchant=:id_merchant';
						$sql_points_sum_ins = 'INSERT INTO tbl_visit_customer_notes (id_user, id_merchant, amount, point, spend_points, visit_times, update_time)
							VALUES (:id_user, :id_merchant, :amount, :point, :spend_points, :visit_times, NOW())';
						$command = $connection->createCommand($sql_points_sum_del);
						$command->execute($params);
						$command = $connection->createCommand($sql_points_sum_ins);
						$params[':amount'] = $r['amounts'];
						$params[':point'] = $current_points;
						$params[':spend_points'] = $redeemed_points;
						$params[':visit_times'] = $r['visit_times'];
						$command->execute($params);
						
						//3. insert the related program if exists
						$program_ids = trim($_POST['program-ids']);
						if (!empty($program_ids)) {
							$sql_visit_id = 'SELECT id FROM tbl_visit_transaction
								WHERE id_user=:id_user AND id_merchant=:id_merchant AND point=:point AND type=2
								AND UNIX_TIMESTAMP(create_time)>:create_time';
							$params_visit[':create_time'] = time()-5;
							$command = $connection->createCommand($sql_visit_id);
							$id_visit = $command->queryScalar($params_visit);
							// insert each program id
							$sql_ins_program = 'INSERT INTO tbl_visit_redeemed_program(id_visit, id_program) VALUES(:id_visit, :id_program)';
							$command = $connection->createCommand($sql_ins_program);
							foreach (explode(',', $program_ids) as $id_program) {
								$id_program = (int)$id_program;
								if ($id_program > 0) {
									$command->execute(array(
										':id_visit' => $id_visit,
										':id_program' => $id_program,
									));
								}
							}
						}
						
						//4. insert into action timeline: reward
						$sql_timeline = 'INSERT INTO tbl_user_action_timeline (id_user, id_merchant, type, message_key, message_values)
							VALUES(:id_user, :id_merchant, :type, :message_key, :message_values)';
						$command = $connection->createCommand($sql_timeline);
						$command->execute(array(
							':id_user' => (int)$row['id'],
							':id_merchant' => (int)Yii::app()->user->id,
							':type' => CustomerHelper::ACTION_REDEEM_REWARD,
							':message_key' => 'program_msg_points_from_user',
							':message_values' => json_encode(array(
									'{pts}' => (float)$model->points,
									'{merchant}' => Yii::app()->user->name,
								)),
						));
						$no_redeem = false;
					}
					
					$connection->active = false;
				} catch (Exception $ex) {
					$connection->active = false;
					BUtils::EmailAdmin('Merchant '.Yii::app()->user->id . ' got Exception at ' . date('Y-m-d'),
						$ex->getMessage() . "\n" . $ex->getTraceAsString());
					
					throw new CHttpException(500, $ex->getMessage());
				}
			} else {
				throw new CHttpException(500, json_encode($model->getErrors()));
			}
		}
		
		$this->renderPartial('_redeem', array('r'=>$r, 'current_points'=>$current_points, 'redeemed_points'=>$model->points, 'no_redeem'=>$no_redeem));
	}
	
	/**
	 * Assign points to customer. Only ajax.
	 */
	public function actionAssign() {
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		
		$r = null;
		$model = new RewardForm();
		
		// collect user input data
		if (isset($_POST['RewardForm'])) {
			$model->points = str_replace('-', '', trim($_POST['RewardForm']['points']));
			$model->customer = trim($_POST['RewardForm']['customer']);
			
			// validate user input and redirect to the previous page if valid
			if ($model->validate()) {
				//start to checking the customers
				$sql_customers = 'SELECT id, email, mobile FROM tbl_user_account
				  WHERE id=:customer OR email=:customer OR mobile=:customer
				  ORDER BY created_time DESC ';
				
				// !important
				// convert mobile phone number: $model->customer
				try {
					$input_type = CustomerHelper::getType($model->customer);
					if (2 == $input_type) {
						$model->customer = CustomerHelper::formatMobile($model->customer);
					}
				} catch (Exception $ex) {
					throw new CHttpException(500, '{"customer":["'.$ex->getMessage().'"]}');
				}
				
				try {
					$connection = Yii::app()->db;
					$command = $connection->createCommand($sql_customers);
					$rows = $command->queryAll(true, array(':customer'=>$model->customer));
					if (count($rows) == 0) {
						//there is no such user found.
						//if email or mobile, then create a new user profile
						//if this is customer ID, popup a error window
						$type = CustomerHelper::getType($model->customer);
						if (0 == $input_type) {
							$connection->active = false;
							throw new Exception('Cannot find this customer with ID: '.$model->customer.'.');
						} else {
							//create user profile: first create with status=999, active user's status=0
							if (1 == $input_type) {
								$sql_user_insert = 'INSERT INTO tbl_user_account (email, status) VALUES (:val, 99)';
								$sql_new_user = 'SELECT id FROM tbl_user_account WHERE email=:customer AND status=99';
							} elseif (2 == $input_type) {
								$sql_user_insert = 'INSERT INTO tbl_user_account (mobile, status) VALUES (:val, 99)';
								$sql_new_user = 'SELECT id FROM tbl_user_account WHERE mobile=:customer AND status=99';
								
								//formated mobile number
								$formated_phone = CustomerHelper::formatMobile($model->customer, 'FI');
								$model->customer = $formated_phone;
							} else {
								$connection->active = false;
								throw new Exception('Cannot find customer: '.$model->customer.'.');
							}
							$command = $connection->createCommand($sql_user_insert);
							$command->execute(array(':val'=>$model->customer));
							$command = $connection->createCommand($sql_new_user);
							$row = $command->queryRow(true, array(':customer'=>$model->customer));

							Yii::log('Merchant '.Yii::app()->user->id.' assigned points '. $model->points .' to new customer: ' . $row['id'],
								CLogger::LEVEL_TRACE, 'web.reward.assign');
						}
					} elseif (count($rows) == 1) {
						//only one customer
						$row = $rows[0];
					} else {
						//there are N user profiles???
						$row = $rows[0];
						//trace error
						$log = 'Merchant '.Yii::app()->user->id.' assign points to the latest user: ' . $row['id'] . '. Find users: ' . print_r($rows, 1);
						Yii::log($log, CLogger::LEVEL_WARNING, 'web.reward.assign');
						BUtils::EmailAdmin('Merchant assign points '.Yii::app()->user->id.': multi-users found!', $log);
					}

					//assign points
					$sql_points = 'INSERT INTO tbl_visit_transaction(id_user, id_merchant, amount, point, type) VALUES(:id_user, :id_merchant, :amount, :point, 1)';
					$params = array(
						':id_user' => $row['id'],
						':id_merchant' => Yii::app()->user->id,
						':amount' => $model->points, ':point' => $model->points,
					);
					$command = $connection->createCommand($sql_points);
					$command->execute($params);

					//new parameters
					$params = array(':id_user'=>$row['id'], ':id_merchant'=>Yii::app()->user->id);
					//1.get the points from customer
					//2.get redeemed points from customer
					$sql_assigned_points = 'SELECT t.id_user, u.name, u.email, u.mobile, u.picture, SUM(t.amount) AS amounts, COUNT(t.id) AS visit_times,
						MAX(UNIX_TIMESTAMP(t.create_time)) AS ts_last_time,
						(SELECT SUM(t1.point) FROM tbl_visit_transaction t1 WHERE t1.id_user=:id_user AND t1.id_merchant=:id_merchant AND t1.type=1) AS all_points,
						(SELECT SUM(t2.point) FROM tbl_visit_transaction t2 WHERE t2.id_user=:id_user AND t2.id_merchant=:id_merchant AND t2.type=2) AS spend_points
						FROM tbl_visit_transaction t
						LEFT JOIN tbl_user_account u ON u.id=t.id_user
						WHERE t.id_user=:id_user AND t.id_merchant=:id_merchant';
					$command = $connection->createCommand($sql_assigned_points);
					$r = $command->queryRow(true, array(':id_user'=>$row['id'], ':id_merchant'=>Yii::app()->user->id));
					
					//update into summary table
					$params = array(':id_user'=>$row['id'], ':id_merchant'=>Yii::app()->user->id);
					$sql_points_sum_del = 'DELETE FROM tbl_visit_customer_notes WHERE id_user=:id_user AND id_merchant=:id_merchant';
					$sql_points_sum_ins = 'INSERT INTO tbl_visit_customer_notes (id_user, id_merchant, amount, point, spend_points, visit_times, update_time)
						VALUES (:id_user, :id_merchant, :amount, :point, :spend_points, :visit_times, NOW())';
					$command = $connection->createCommand($sql_points_sum_del);
					$command->execute($params);
					$command = $connection->createCommand($sql_points_sum_ins);
					$params[':amount'] = $r['amounts'];
					$params[':point'] = $r['all_points'] + $r['spend_points'];
					$params[':spend_points'] = -$r['spend_points'];
					$params[':visit_times'] = $r['visit_times'];
					$command->execute($params);
					
					//sumerize points for this user
					//select user_id, email, mobile, app_device, app_type, lang, status
					$sql_notify_user = 'SELECT u.id, u.name, u.mobile, u.email, u.lang, u.status, n.point, n.visit_times, d.device_notification_token, d.device_type
						FROM tbl_user_account u
						LEFT JOIN tbl_visit_customer_notes n ON u.id=n.id_user AND n.id_merchant=:id_merchant '
						. " LEFT JOIN tbl_user_device_settings d ON u.id=d.id_user AND (d.device_notification_token IS NOT NULL AND d.device_notification_token<>'')"
						. ' WHERE u.id=:id_user
						ORDER BY d.update_time DESC
						LIMIT 1';
					$sql_program_points = 'SELECT required_points
						FROM tbl_merchant_program
						WHERE id_merchant=:id_merchant AND status=1 AND trash=0
						ORDER BY required_points ASC
						LIMIT 1';
					$command = $connection->createCommand($sql_notify_user);
					$rUser = $command->queryRow(true, array(':id_user'=>$row['id'], ':id_merchant'=>Yii::app()->user->id));
					$command = $connection->createCommand($sql_program_points);
					$required_points = $command->queryScalar(array(':id_merchant'=>Yii::app()->user->id));
					
					//insert into action timeline: point
					$sql_timeline = 'INSERT INTO tbl_user_action_timeline (id_user, id_merchant, type, message_key, message_values)
						VALUES(:id_user, :id_merchant, :type, :message_key, :message_values)';
					$command = $connection->createCommand($sql_timeline);
					$command->execute(array(
						':id_user' => (int)$rUser['id'],
						':id_merchant' => (int)Yii::app()->user->id,
						':type' => CustomerHelper::ACTION_EARN_POINT,
						':message_key' => 'program_msg_points_to_user',
						':message_values' => json_encode(array(
								'{pts}' => (float)$model->points,
								'{merchant}' => Yii::app()->user->name,
							)),
					));
					$connection->active = false;
					
					//send notification: assign points
					//get the langage code, default is en_gb
					$lang_code = 'fi_fi';
					if (empty($rUser['lang'])) $lang_code = isset($_POST['notify']['lang']) ? $_POST['notify']['lang'] : 'fi_fi';
					else $lang_code = BUtils::GetLanguageCode($rUser['lang']);

					//notify user with the following message
					$message = Yii::t('message', 'program_points_to_user_msg', array(
						'{pts}' => $model->points,
						'{merchant}' => Yii::app()->user->name,
						'{url_rewards}' => 'http://yamii.fi/place/' . Yii::app()->user->id,
					), null, $lang_code);
					
					//find the notification method
					//TODO: later add the subscribe table to enable user notification preference
					//用户在自己的profile里面选择通知的方式app/email/sms，推荐方式为app
					$targets = array();
					if (!empty($rUser['email'])) {
						if (!empty($message)) $message = "Hi {$rUser['name']},<br/><br/>\n{$message}<br/><br/>\nBR,<br/>\nYamii Team<br/>\nhttp://yamii.fi/<br/>\nhttp://facebook.com/yamiideal<br/>\n";
						$targets[] = array(
							'to' => $rUser['email'],
							'method' => 'email',
							'message' => $message,
							'title' => 'Notification',
						);
					} elseif (!empty($rUser['mobile'])) {
						$targets[] = array(
							'to' => $rUser['mobile'],
							'method' => 'sms',
							'message' => $message,
						);
					} elseif (!empty($rUser['device_notification_token'])) {
						$targets[] = array(
							'to' => $rUser['device_notification_token'],
							'method' => $rUser['device_type'],
							'message' => $message,
						);
					}
					CustomerHelper::notifyUser($targets);
					
				} catch (Exception $ex) {
					$connection->active = false;
					BUtils::EmailAdmin('Merchant '.Yii::app()->user->id . ' got Exception at ' . date('Y-m-d'),
						$ex->getMessage() . ' | ' . $ex->getTraceAsString());
					
					throw new CHttpException(500, '{"customer":["'.$ex->getMessage().'"]}');
				}
			} else {
				throw new CHttpException(404, json_encode($model->getErrors()));
			}
		}
		
		$this->renderPartial('_assign', array('r'=>$r, 'assigned_points'=>$model->points));
	}
}